require 'sequel'
require 'sequel/sqlite'

DB = Sequel.open 'sqlite:///orders.db'

DB.create_table :orders do
  primary_key :id
  column :stamp, :timestamp
  foreign_key :client_id, :table => :clients, :on_delete => :set_null
end

DB.create_table :items do
  primary_key :id
  column :name, :text
  column :price, :float
end

DB.create_table :order_items do
  foreign_key :order_id, :table => :orders, :on_delete => :cascade
  foreign_key :item_id, :table => :items, :on_delete => :set_null
end


class Order < Sequel::Model(:orders)
  many_to_many :items, :class => Item, :through => :order_items
  # implies self_foreign_key => :order_id, which is qualified to :order_items__order_id
  # implies relation_foreign_key => Item.primary_key = :id, qualified to :items__id
  
  # creates the following method:
  def items
    Item.join(:order_items, :item_id => :items__id).
      filter(:order_items__order_id => @pkey)
  end
end

class Item < Sequel::Model(:items)
  many_to_many :orders, :class => Order, :through => :order_items
  # implies self_foreign_key => :item_id, which is qualified to :order_items__item_id
  # implies relation_foreign_key => Order.primary_key = :id, qualified to :orders__id
  
  # creates the following method:
  def items
    Item.join(:order_items, :order_id => :orders__id).
      filter(:order_items__item_id => @pkey)
  end
end

